﻿using DBHelpers;
using EntityModel.PageListInfor;
using IServices.ICurrency;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.International.Converters.PinYinConverter;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Services.Currency
{
    /// <summary>
    /// 通用接口实现类
    /// </summary>
    public class CurrencyServer : Controller, ICurrencyServer
    {
        private readonly IConfiguration _configuration;
        DataHelper _dataHelper = new DataHelper();
        string _connerction = string.Empty;
        public CurrencyServer(IConfiguration configuration)
        {
            _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration));
            _connerction = _configuration.GetConnectionString("DefaultConnection");
            //_appconfig = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
        }

        /// <summary>
        /// 失败时返回信息
        /// </summary>
        /// <param name="obj">失败信息</param>
        /// <returns></returns>
        public IActionResult Fail(string obj)
        {
            return Content(JsonConvert.SerializeObject(new ResParameter { code = ResponseCode.fail, info = obj }));
        }

        /// <summary>
        /// 成功时返回信息
        /// </summary>
        /// <param name="obj">成功返回信息</param>
        /// <returns></returns>
        public IActionResult Success(object obj)
        {
            return Content(JsonConvert.SerializeObject(
                new ResParameter { code = ResponseCode.success, info = "" + obj + "", data = obj }));
        }

        /// <summary>
        /// 执行增删改查sql语句
        /// </summary>
        /// <param name="coon">数据库连接串</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public int DeletUpdateCreateSql(string coon, string sql)
        {
            if (coon == null || coon == "") throw new ArgumentNullException(nameof(coon));
            if (sql == null || sql == "") throw new ArgumentNullException(nameof(sql));
            try
            {
                return Convert.ToInt32(_dataHelper.PerformDeleUpdateAdd(coon, sql));
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行查询语句返回DataTable
        /// </summary>
        /// <param name="crmSqlConnect">数据库连接串</param>
        /// <param name="sql">执行sql语句</param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteSqlAsync(string crmSqlConnect, string sql, SqlParameter[] sqlParameters = null)
        {
            if (sql == null || sql == "") throw new ArgumentNullException(nameof(sql));
            try
            {
                return _dataHelper.ExecuteQueryDatable(_connerction, sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 使用SQL Server 的Row_Number()函数进行分页查询的方法
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="pk">主键</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前显示行数</param>
        /// <param name="sortBy">排序字段：只能对应一个字段</param>
        /// <param name="sortOrder">排序类型</param>
        /// <param name="filter">查询条件：不带WHERE，可以是多个条件的组合</param>
        /// <param name="isCount">是否显示总数</param>
        /// <param name="selectFields">查询输出字段列表</param>
        /// <returns></returns>
        public async Task<DbQueryResult> PagingByRownumber(string table, string pk, int pageIndex, int pageSize, string sortBy
            , SortAction sortOrder, string filter, bool isCount, string selectFields)
        {
            return await PagingByRownumber(table, pk, pageIndex, pageSize, sortBy, sortOrder, filter, isCount, selectFields, 0);
        }


        /// <summary>
        /// 使用SQL Server 的Row_Number()函数进行分页查询的方法
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="key">主键</param>
        /// <param name="pageindex">当前页</param>
        /// <param name="pagesize">当前显示条数</param>
        /// <param name="sortby">排序字段</param>
        /// <param name="sortAction">排序类型</param>
        /// <param name="filter">查询条件</param>
        /// <param name="isCount">是否统计总数</param>
        /// <param name="selectFields">查询输出字段列表</param>
        /// <param name="partitionSize">是否限制只返回符合查询条件的前多少条记录，为0表示不限制</param>
        /// <returns></returns>
        public async Task<DbQueryResult> PagingByRownumber(string table, string key, int pageindex, int pagesize,
            string sortby, SortAction sortAction, string filter, bool isCount, string selectFields, int partitionSize)
        {
            if (string.IsNullOrEmpty(table) && string.IsNullOrEmpty(sortby) && string.IsNullOrEmpty(key))
            {
                // Row_Number()函数必须要有一个排序字段
                // 如果指定了sortby则用sortby，否则使用key，如果两者都没指定则无法查询
                return null;
            }
            if (string.IsNullOrEmpty(selectFields))
            {
                // 如果没有指定输出列表，则输出所有字段
                selectFields = "*";
            }
            int startNumber = (pageindex - 1) * pagesize + 1;
            int endNumber = startNumber + pagesize - 1;
            string query = BuildRownumberQuery(sortby, sortAction, isCount, table, key, filter, selectFields, partitionSize, startNumber, endNumber);
            string filterExpression = string.IsNullOrEmpty(filter) ? "" : "where " + filter;
            DbQueryResult result = new DbQueryResult();
            try
            {
                if (isCount && partitionSize == 0)
                {
                    result.Data = ExecuteSqlAsync(_connerction, query);
                    var Total = ExecuteSqlAsync(_connerction, $"; select count(*) count from {table} {filterExpression}");
                    int to = new int();
                    foreach (DataRow item in Total.Rows)
                    {
                        to = Convert.ToInt32(item["count"]);
                    }
                    result.TotalRecords = to;
                }

                //Database database = DatabaseFactory.CreateDatabase();
                //DbCommand dbCmd = database.GetSqlStringCommand(query);
                //database.AddInParameter(dbCmd, "StartNumber", DbType.Int32, startNumber);
                //database.AddInParameter(dbCmd, "EndNumber", DbType.Int32, endNumber);
                //using (IDataReader reader = database.ExecuteReader(dbCmd))
                //{
                //    result.Data = _dataHelper.ConverDataReaderToDataTable(reader);
                //    if (isCount && partitionSize == 0 && reader.NextResult())
                //    {
                //        // 只要指定了partitionSize，分页查询就无法返回正确的符合查询的总记录数
                //        // 所以只有当没有指定partitionSize的时候才返回总记录数
                //        reader.Read();
                //        result.TotalRecords = reader.GetInt32(0);
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 将查询条件拼接成sql语句
        /// </summary>
        /// <param name="sortby">排序字段</param>
        /// <param name="sortAction">排序方式：0=desc,1=asc</param>
        /// <param name="isCount">是否统计总数</param>
        /// <param name="table">表名</param>
        /// <param name="key">主键</param>
        /// <param name="filter">查询条件</param>
        /// <param name="selectFields">查询输出字段列表</param>
        /// <param name="partitionSize">是否限制只返回符合查询条件的前多少条记录，为0表示不限制</param>
        /// <returns></returns>
        private static string BuildRownumberQuery(string sortby, SortAction sortAction, bool isCount
            , string table, string key, string filter, string selectFields, int partitionSize, int startNumber, int endNumber)
        {
            StringBuilder sb = new StringBuilder();
            if (partitionSize > 0)
            {
                sb.AppendFormat(@$"SELECT TOP {partitionSize.ToString(CultureInfo.InvariantCulture)} {selectFields} , 
                                ROW_NUMBER() OVER (ORDER BY");
            }
            else
            {
                sb.AppendFormat($"SELECT {selectFields} ,ROW_NUMBER() OVER (ORDER BY ");
            }
            sb.AppendFormat("{0} {1}", string.IsNullOrEmpty(sortby) ? key : sortby, sortAction.ToString());
            string filterExpression = string.IsNullOrEmpty(filter) ? "" : "where " + filter;
            sb.AppendFormat(@$") AS RowNumber From {table} {filterExpression}");
            //sb.Insert(0, "select * from (").Append(") T where T.RowNumber BETWEEN @StartNumber AND @EndNumber order by T.RowNumber");
            sb.Insert(0, "select * from (").Append(") T where T.RowNumber BETWEEN " + startNumber + " AND " + endNumber + " order by T.RowNumber");
            //if (isCount && partitionSize == 0)
            //{
            //    sb.AppendFormat($"; select count(*) from {table} {filterExpression}");
            //}
            return sb.ToString();
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private int IsTable(string table)
        {
            var result = ExecuteSqlAsync(_connerction, $"select count(1) from sys.objects where name = '{table}'");
            return result.Rows.Count > 0 ? result.Rows.Count : 0;
        }

        /// <summary>
        /// 中文转英文（获取中文首字母）
        /// </summary>
        /// <param name="chinese">中文汉字</param>
        /// <returns></returns>
        public async Task<string> InTheSwap(string chinese)
        {
            if (chinese.Length == 0) return null;
            string result = string.Empty;
            Encoding gb2312 = Encoding.GetEncoding("GB2312");
            foreach (var item in chinese.ToList())
            {
                result = Pinyin.ConvertEncoding(item.ToString(), Encoding.UTF8, gb2312);
            }
            return result;
        }
    }
}
